﻿CREATE FUNCTION [Mapping].[GetSQL_CreateRuntimeView]
(
  @output nvarchar(4000),
  @pivot nvarchar(4000),
  @keyType nvarchar(20),
  @groupId int,
  @viewName nvarchar(50)
)
RETURNS nvarchar(4000)
AS
BEGIN
  DECLARE @sql nvarchar(4000)
  SET @sql = 
    '
    CREATE VIEW [Mapping].[<{ViewName}>]
      AS 
      SELECT
        <{SourceKeyOutput}>,
        [Priority], [Masked], [Enabled],
        [TargetKeyValue],
        CAST([TargetKeyAlternateValue] AS <{KeyType}>) AS KeyAlternateValue
      FROM Mapping.vMappingItemView
      LEFT JOIN (
        SELECT * 
          FROM (
            SELECT MappingItem, KeyColumn, KeyValue 
              FROM Mapping.SourceKey 
          ) t
          PIVOT (MAX(KeyValue) FOR KeyColumn IN (<{SourceKeyPivot}>)) p
      ) SourceKey ON SourceKey.MappingItem = vMappingItemView.Id
      WHERE [GroupId] = <{GroupId}>'

  SET @sql = REPLACE(@sql, '<{ViewName}>', @viewName)
  SET @sql = REPLACE(@sql, '<{SourceKeyOutput}>', @output)
  SET @sql = REPLACE(@sql, '<{SourceKeyPivot}>', @pivot)
  SET @sql = REPLACE(@sql, '<{KeyType}>', @keyType)
  SET @sql = REPLACE(@sql, '<{GroupId}>', CAST(@groupId AS nvarchar(30)))

  RETURN @sql
END
